package com.report;

import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;

public class PoiDemo {

	private static final int sWidth = 200;
	public static void main(String[] args) throws Exception {

		OutputStream os = new FileOutputStream("d:/demo.xls");

		Workbook hwb = new HSSFWorkbook();
		Font font = hwb.createFont();
		font.setBold(true);
		CellStyle style = hwb.createCellStyle();
		style.setFont(font);

		Sheet hSheet = (HSSFSheet) hwb.createSheet("sheet1");
//		for (int i = 0; i < 32; i++) {
//			if (i < 3) {
//				hSheet.setColumnWidth(i, 800);
//			} else {
//				hSheet.setColumnWidth(i, 1600);
//			}
//		}
		
		Map<String, CellStyle> styleMap = initStyleMap(hwb, hSheet);

		HSSFRow row = (HSSFRow) hSheet.createRow(0);
		HSSFCell cell = (HSSFCell) row.createCell(0);
		cell.setCellValue("附件1");
		cell.setCellStyle(styleMap.get("REMARKSTYLE1"));
		
		row = (HSSFRow) hSheet.createRow(1);
		cell = (HSSFCell) row.createCell(0);
		cell.setCellValue("自主研发“研发支出”辅助账（样式）");
		hSheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 32));
		cell.setCellStyle(styleMap.get("HEADSTYLE1"));
		
		row = (HSSFRow) hSheet.createRow(2);
		
		cell = (HSSFCell) row.createCell(0);
		CellRangeAddress range = new CellRangeAddress(2, 2, 0, 3);
		hSheet.addMergedRegion(range);
		setRegionWidth("项目名称：".getBytes().length * sWidth, range, hSheet);
		cell.setCellValue("项目名称：");
		cell.setCellStyle(styleMap.get("HEADSTYLE2"));
		
		hSheet.setColumnWidth(4, hSheet.getColumnWidth(0));
		
		cell = (HSSFCell) row.createCell(5);
		range = new CellRangeAddress(2, 2, 5, 6);
		hSheet.addMergedRegion(range);
		cell.setCellValue("项目编号：");
		setRegionWidth("项目编号：".getBytes().length * sWidth, range, hSheet);
		cell.setCellStyle(styleMap.get("HEADSTYLE2"));
		
		hSheet.setColumnWidth(7, hSheet.getColumnWidth(6));
		hSheet.setColumnWidth(8, hSheet.getColumnWidth(6));
		
		cell = (HSSFCell) row.createCell(9);
		range = new CellRangeAddress(2, 2, 9, 12);
		hSheet.addMergedRegion(new CellRangeAddress(2, 2, 9, 12));
		cell.setCellValue("资本化、费用化支出选项：");
		setRegionWidth("资本化、费用化支出选项：".getBytes().length * sWidth, range, hSheet);
		cell.setCellStyle(styleMap.get("HEADSTYLE2"));
		
		cell = (HSSFCell) row.createCell(13);
		cell.setCellValue("◎资本化：");
		hSheet.setColumnWidth(13, "◎资本化：".getBytes().length * sWidth);
		cell.setCellStyle(styleMap.get("HEADSTYLE3"));
		
		cell = (HSSFCell) row.createCell(14);
		range = new CellRangeAddress(2, 2, 14, 15);
		hSheet.addMergedRegion(range);
		cell.setCellValue("○费用化：");
		setRegionWidth("○费用化：".getBytes().length * sWidth, range, hSheet);
		cell.setCellStyle(styleMap.get("HEADSTYLE3"));
		
		cell = (HSSFCell) row.createCell(16);
		range = new CellRangeAddress(2, 2, 16, 18);
		hSheet.addMergedRegion(range);
		cell.setCellValue("项目实施状态选项：");
		setRegionWidth("项目实施状态选项：".getBytes().length * sWidth, range, hSheet);
		cell.setCellStyle(styleMap.get("HEADSTYLE2"));
		
		cell = (HSSFCell) row.createCell(19);
		cell.setCellValue("◎未完成：");
		hSheet.setColumnWidth(19, "◎未完成：".getBytes().length * sWidth);
		cell.setCellStyle(styleMap.get("HEADSTYLE3"));
		
		cell = (HSSFCell) row.createCell(20);
		range = new CellRangeAddress(2, 2, 20, 21);
		hSheet.addMergedRegion(range);
		cell.setCellValue("○已完成：");
		setRegionWidth("○已完成：".getBytes().length * sWidth, range, hSheet);
		cell.setCellStyle(styleMap.get("HEADSTYLE3"));
		
		cell = (HSSFCell) row.createCell(22);
		range = new CellRangeAddress(2, 2, 22, 23);
		hSheet.addMergedRegion(range);
		cell.setCellValue("研发成果：");
		setRegionWidth("研发成果：".getBytes().length * sWidth, range, hSheet);
		cell.setCellStyle(styleMap.get("HEADSTYLE2"));
		
		cell = (HSSFCell) row.createCell(25);
		range = new CellRangeAddress(2, 2, 25, 27);
		hSheet.addMergedRegion(range);
		cell.setCellValue("研发成果证书号：");
		setRegionWidth("研发成果证书号：".getBytes().length * sWidth, range, hSheet);
		cell.setCellStyle(styleMap.get("HEADSTYLE2"));
		
		cell = (HSSFCell) row.createCell(31);
		range = new CellRangeAddress(2, 2, 28, 32);
		hSheet.addMergedRegion(range);
		cell.setCellValue("金额单位：元（列至角分）：");
		setRegionWidth("金额单位：元（列至角分）：".getBytes().length * sWidth, range, hSheet);
		cell.setCellStyle(styleMap.get("HEADSTYLE2"));
		
		row = (HSSFRow) hSheet.createRow(3);
		
		cell = (HSSFCell) row.createCell(0);
		range = new CellRangeAddress(3, 5, 0, 1);
		hSheet.addMergedRegion(range);
		cell.setCellValue("20XX 年");
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		//放到 setCellStyle 下方
		setRegionBorder(new Integer[]{1, 1, 1, 2}, range, hSheet, hwb);
		
		cell = (HSSFCell) row.createCell(2);
		range = new CellRangeAddress(3, 5, 2, 3);
		hSheet.addMergedRegion(range);
		cell.setCellValue("凭证");
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		setRegionBorder(new Integer[]{1, 1, 1, 2}, range, hSheet, hwb);
		
		cell = (HSSFCell) row.createCell(4);
		range = new CellRangeAddress(3, 6, 4, 4);
		hSheet.addMergedRegion(range);
		cell.setCellValue("摘要");
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		setRegionBorder(new Integer[]{1, 1, 1, 2}, range, hSheet, hwb);
		
		cell = (HSSFCell) row.createCell(5);
		range = new CellRangeAddress(3, 6, 5, 5);
		hSheet.addMergedRegion(range);
		cell.setCellValue("借方金额");
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		setRegionBorder(new Integer[]{1, 1, 1, 2}, range, hSheet, hwb);
		
		cell = (HSSFCell) row.createCell(6);
		range = new CellRangeAddress(3, 6, 6, 6);
		hSheet.addMergedRegion(range);
		cell.setCellValue("贷方金额");
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		setRegionBorder(new Integer[]{1, 1, 1, 2}, range, hSheet, hwb);
		
		cell = (HSSFCell) row.createCell(7);
		range = new CellRangeAddress(3, 6, 7, 7);
		hSheet.addMergedRegion(range);
		cell.setCellValue("借或贷");
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		setRegionBorder(new Integer[]{1, 1, 1, 2}, range, hSheet, hwb);
		
		cell = (HSSFCell) row.createCell(8);
		range = new CellRangeAddress(3, 6, 8, 8);
		hSheet.addMergedRegion(range);
		cell.setCellValue("余额");
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		setRegionBorder(new Integer[]{1, 1, 1, 2}, range, hSheet, hwb);
		
		cell = (HSSFCell) row.createCell(9);
		range = new CellRangeAddress(3, 3, 9, 32);
		hSheet.addMergedRegion(range);
		cell.setCellValue("费用明细（借方）");
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		setRegionBorder(new Integer[]{1, 1, 2, 2}, range, hSheet, hwb);
		
		row = (HSSFRow) hSheet.createRow(4);
		createBlanCell(styleMap, row, 9);
		
		cell = (HSSFCell) row.createCell(9);
		range = new CellRangeAddress(4, 4, 9, 11);
		hSheet.addMergedRegion(range);
		cell.setCellValue("一、人员人工费用");
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		setRegionBorder(new Integer[]{1, 1, 1, 1}, range, hSheet, hwb);
		
		cell = (HSSFCell) row.createCell(12);
		range = new CellRangeAddress(4, 4, 12, 19);
		hSheet.addMergedRegion(range);
		cell.setCellValue("二、直接投入费用");
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		setRegionBorder(new Integer[]{1, 1, 1, 1}, range, hSheet, hwb);
		
		cell = (HSSFCell) row.createCell(20);
		range = new CellRangeAddress(4, 4, 20, 21);
		hSheet.addMergedRegion(range);
		cell.setCellValue("三、折旧费用");
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		setRegionBorder(new Integer[]{1, 1, 1, 1}, range, hSheet, hwb);
		
		cell = (HSSFCell) row.createCell(22);
		range = new CellRangeAddress(4, 4, 22, 24);
		hSheet.addMergedRegion(range);
		cell.setCellValue("四、无形资产摊销");
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		setRegionBorder(new Integer[]{1, 1, 1, 1}, range, hSheet, hwb);
		
		cell = (HSSFCell) row.createCell(25);
		range = new CellRangeAddress(4, 4, 25, 28);
		hSheet.addMergedRegion(range);
		cell.setCellValue("五、新产品设计费等");
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		setRegionBorder(new Integer[]{1, 1, 1, 1}, range, hSheet, hwb);
		
		cell = (HSSFCell) row.createCell(29);
		range = new CellRangeAddress(4, 4, 29, 32);
		hSheet.addMergedRegion(range);
		cell.setCellValue("六、其他相关费用");
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		setRegionBorder(new Integer[]{1, 1, 2, 1}, range, hSheet, hwb);
		
		row = (HSSFRow) hSheet.createRow(5);
		createBlanCell(styleMap, row, 9);
		
		cell = (HSSFCell) row.createCell(9);
		range = new CellRangeAddress(5, 5, 9, 10);
		hSheet.addMergedRegion(range);
		cell.setCellValue("直接从事研发活动人员");
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		setRegionBorder(new Integer[]{1, 1, 1, 1}, range, hSheet, hwb);
		
		cell = (HSSFCell) row.createCell(11);
		range = new CellRangeAddress(5, 6, 11, 11);
		hSheet.addMergedRegion(range);
		cell.setCellValue("外聘研发人员的劳务费用");
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		setRegionBorder(new Integer[]{1, 1, 1, 1}, range, hSheet, hwb);
		
		cell = (HSSFCell) row.createCell(12);
		range = new CellRangeAddress(5, 5, 12, 14);
		hSheet.addMergedRegion(range);
		cell.setCellValue("研发活动直接消耗");
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		setRegionBorder(new Integer[]{1, 1, 1, 1}, range, hSheet, hwb);
		
		cell = (HSSFCell) row.createCell(15);
		range = new CellRangeAddress(5, 6, 15, 15);
		hSheet.addMergedRegion(range);
		cell.setCellValue("用于中间试验和产品试制的模具、工艺装备开发及制造费");
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		setRegionBorder(new Integer[]{1, 1, 1, 1}, range, hSheet, hwb);
		
		cell = (HSSFCell) row.createCell(16);
		range = new CellRangeAddress(5, 6, 16, 16);
		hSheet.addMergedRegion(range);
		cell.setCellValue("用于不构成固定资产的样品、样机及一般测试手段购置费");
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		setRegionBorder(new Integer[]{1, 1, 1, 1}, range, hSheet, hwb);
		
		cell = (HSSFCell) row.createCell(17);
		range = new CellRangeAddress(5, 6, 17, 17);
		hSheet.addMergedRegion(range);
		cell.setCellValue("用于试制产品的检验费");
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		setRegionBorder(new Integer[]{1, 1, 1, 1}, range, hSheet, hwb);
		
		cell = (HSSFCell) row.createCell(18);
		range = new CellRangeAddress(5, 6, 18, 18);
		hSheet.addMergedRegion(range);
		cell.setCellValue("用于研发活动的仪器、设备的运行维护、调整、检验、维修等费用");
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		setRegionBorder(new Integer[]{1, 1, 1, 1}, range, hSheet, hwb);
		
		cell = (HSSFCell) row.createCell(19);
		range = new CellRangeAddress(5, 6, 19, 19);
		hSheet.addMergedRegion(range);
		cell.setCellValue("通过经营租赁方式租入的用于研发活动的仪器、设备租赁费");
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		setRegionBorder(new Integer[]{1, 1, 1, 1}, range, hSheet, hwb);
		
		cell = (HSSFCell) row.createCell(20);
		range = new CellRangeAddress(5, 6, 20, 20);
		hSheet.addMergedRegion(range);
		cell.setCellValue("用于研发活动的仪器的折旧费");
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		setRegionBorder(new Integer[]{1, 1, 1, 1}, range, hSheet, hwb);
		
		cell = (HSSFCell) row.createCell(21);
		range = new CellRangeAddress(5, 6, 21, 21);
		hSheet.addMergedRegion(range);
		cell.setCellValue("用于研发活动的设备的折旧费");
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		setRegionBorder(new Integer[]{1, 1, 1, 1}, range, hSheet, hwb);
		
		cell = (HSSFCell) row.createCell(22);
		range = new CellRangeAddress(5, 6, 22, 22);
		hSheet.addMergedRegion(range);
		cell.setCellValue("用于研发活动的软件的摊销费用");
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		setRegionBorder(new Integer[]{1, 1, 1, 1}, range, hSheet, hwb);
		
		cell = (HSSFCell) row.createCell(23);
		range = new CellRangeAddress(5, 6, 23, 23);
		hSheet.addMergedRegion(range);
		cell.setCellValue("用于研发活动的专利权的摊销费用");
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		setRegionBorder(new Integer[]{1, 1, 1, 1}, range, hSheet, hwb);
		
		cell = (HSSFCell) row.createCell(24);
		range = new CellRangeAddress(5, 6, 24, 24);
		hSheet.addMergedRegion(range);
		cell.setCellValue("用于研发活动的非专利技术（包括许可证、专有技术、设计和计算方法等）的摊销费用");
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		setRegionBorder(new Integer[]{1, 1, 1, 1}, range, hSheet, hwb);
		
		cell = (HSSFCell) row.createCell(25);
		range = new CellRangeAddress(5, 6, 25, 25);
		hSheet.addMergedRegion(range);
		cell.setCellValue("新产品设计费");
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		setRegionBorder(new Integer[]{1, 1, 1, 1}, range, hSheet, hwb);
		
		cell = (HSSFCell) row.createCell(26);
		range = new CellRangeAddress(5, 6, 26, 26);
		hSheet.addMergedRegion(range);
		cell.setCellValue("新工艺规程制定费");
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		setRegionBorder(new Integer[]{1, 1, 1, 1}, range, hSheet, hwb);
		
		cell = (HSSFCell) row.createCell(27);
		range = new CellRangeAddress(5, 6, 27, 27);
		hSheet.addMergedRegion(range);
		cell.setCellValue("新药研制的临床试验费");
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		setRegionBorder(new Integer[]{1, 1, 1, 1}, range, hSheet, hwb);
		
		cell = (HSSFCell) row.createCell(28);
		range = new CellRangeAddress(5, 6, 28, 28);
		hSheet.addMergedRegion(range);
		cell.setCellValue("勘探开发技术的现场试验费");
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		setRegionBorder(new Integer[]{1, 1, 1, 1}, range, hSheet, hwb);
		
		cell = (HSSFCell) row.createCell(29);
		range = new CellRangeAddress(5, 6, 29, 29);
		hSheet.addMergedRegion(range);
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		setRegionBorder(new Integer[]{1, 1, 1, 1}, range, hSheet, hwb);
		
		cell = (HSSFCell) row.createCell(30);
		range = new CellRangeAddress(5, 6, 30, 30);
		hSheet.addMergedRegion(range);
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		setRegionBorder(new Integer[]{1, 1, 1, 1}, range, hSheet, hwb);
		
		cell = (HSSFCell) row.createCell(31);
		range = new CellRangeAddress(5, 6, 31, 31);
		hSheet.addMergedRegion(range);
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		setRegionBorder(new Integer[]{1, 1, 1, 1}, range, hSheet, hwb);
		
		cell = (HSSFCell) row.createCell(32);
		range = new CellRangeAddress(5, 6, 32, 32);
		hSheet.addMergedRegion(range);
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		setRegionBorder(new Integer[]{1, 1, 2, 1}, range, hSheet, hwb);
		
		row = (HSSFRow) hSheet.createRow(6);
		
		cell = (HSSFCell) row.createCell(0);
		range = new CellRangeAddress(6, 7, 0, 0);
		hSheet.addMergedRegion(range);
		cell.setCellValue("月");
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		setRegionBorder(new Integer[]{1, 1, 1, 1}, range, hSheet, hwb);
		
		cell = (HSSFCell) row.createCell(1);
		range = new CellRangeAddress(6, 7, 1, 1);
		hSheet.addMergedRegion(range);
		cell.setCellValue("日");
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		setRegionBorder(new Integer[]{1, 1, 1, 1}, range, hSheet, hwb);
		
		cell = (HSSFCell) row.createCell(2);
		range = new CellRangeAddress(6, 7, 2, 2);
		hSheet.addMergedRegion(range);
		cell.setCellValue("种类");
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		setRegionBorder(new Integer[]{1, 1, 1, 1}, range, hSheet, hwb);
		
		cell = (HSSFCell) row.createCell(3);
		range = new CellRangeAddress(6, 7, 3, 3);
		hSheet.addMergedRegion(range);
		cell.setCellValue("号数");
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		setRegionBorder(new Integer[]{1, 1, 1, 1}, range, hSheet, hwb);
		
		createBlanCell(styleMap, row, 4, 9);
		
		cell = (HSSFCell) row.createCell(9);
		cell.setCellValue("工资薪金");
		cell.setCellStyle(styleMap.get("HEADSTYLE5"));
		
		cell = (HSSFCell) row.createCell(10);
		cell.setCellValue("五险一金");
		cell.setCellStyle(styleMap.get("HEADSTYLE5"));
		
		cell = (HSSFCell) row.createCell(12);
		cell.setCellValue("材料");
		cell.setCellStyle(styleMap.get("HEADSTYLE5"));
		
		cell = (HSSFCell) row.createCell(13);
		cell.setCellValue("燃料");
		cell.setCellStyle(styleMap.get("HEADSTYLE5"));
		
		cell = (HSSFCell) row.createCell(14);
		cell.setCellValue("动力费用");
		cell.setCellStyle(styleMap.get("HEADSTYLE5"));
		
		cell = (HSSFCell) row.createCell(15);
		cell.setCellValue("动力费用");
		cell.setCellStyle(styleMap.get("HEADSTYLE5"));
		
		createBlanCell(styleMap, row, 16, 33);
		
		
		row = (HSSFRow) hSheet.createRow(7);
		createBlanCell(styleMap, row, 4);
		
		cell = (HSSFCell) row.createCell(4);
		range = new CellRangeAddress(7, 7, 4, 8);
		hSheet.addMergedRegion(range);
		cell.setCellValue("序号");
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		setRegionBorder(new Integer[]{1, 1, 1, 1}, range, hSheet, hwb);
		
		cell = (HSSFCell) row.createCell(9);
		cell.setCellValue("1.1");
		cell.setCellStyle(styleMap.get("HEADSTYLE5"));
		
		cell = (HSSFCell) row.createCell(10);
		cell.setCellValue("1.2");
		cell.setCellStyle(styleMap.get("HEADSTYLE5"));
		
		cell = (HSSFCell) row.createCell(11);
		cell.setCellValue("1.3");
		cell.setCellStyle(styleMap.get("HEADSTYLE5"));
		
		cell = (HSSFCell) row.createCell(12);
		cell.setCellValue("2.1");
		cell.setCellStyle(styleMap.get("HEADSTYLE5"));
		
		cell = (HSSFCell) row.createCell(13);
		cell.setCellValue("2.2");
		cell.setCellStyle(styleMap.get("HEADSTYLE5"));
		
		cell = (HSSFCell) row.createCell(14);
		cell.setCellValue("2.3");
		cell.setCellStyle(styleMap.get("HEADSTYLE5"));
		
		cell = (HSSFCell) row.createCell(15);
		cell.setCellValue("2.4");
		cell.setCellStyle(styleMap.get("HEADSTYLE5"));
		
		cell = (HSSFCell) row.createCell(16);
		cell.setCellValue("2.5");
		cell.setCellStyle(styleMap.get("HEADSTYLE5"));
		
		cell = (HSSFCell) row.createCell(17);
		cell.setCellValue("2.6");
		cell.setCellStyle(styleMap.get("HEADSTYLE5"));
		
		cell = (HSSFCell) row.createCell(18);
		cell.setCellValue("2.7");
		cell.setCellStyle(styleMap.get("HEADSTYLE5"));
		
		cell = (HSSFCell) row.createCell(19);
		cell.setCellValue("2.8");
		cell.setCellStyle(styleMap.get("HEADSTYLE5"));
		
		cell = (HSSFCell) row.createCell(20);
		cell.setCellValue("3.1");
		cell.setCellStyle(styleMap.get("HEADSTYLE5"));
		
		cell = (HSSFCell) row.createCell(21);
		cell.setCellValue("3.2");
		cell.setCellStyle(styleMap.get("HEADSTYLE5"));
		
		cell = (HSSFCell) row.createCell(22);
		cell.setCellValue("4.1");
		cell.setCellStyle(styleMap.get("HEADSTYLE5"));
		
		cell = (HSSFCell) row.createCell(23);
		cell.setCellValue("4.2");
		cell.setCellStyle(styleMap.get("HEADSTYLE5"));
		
		cell = (HSSFCell) row.createCell(24);
		cell.setCellValue("4.3");
		cell.setCellStyle(styleMap.get("HEADSTYLE5"));
		
		cell = (HSSFCell) row.createCell(25);
		cell.setCellValue("5.1");
		cell.setCellStyle(styleMap.get("HEADSTYLE5"));
		
		cell = (HSSFCell) row.createCell(26);
		cell.setCellValue("5.2");
		cell.setCellStyle(styleMap.get("HEADSTYLE5"));
		
		cell = (HSSFCell) row.createCell(27);
		cell.setCellValue("5.3");
		cell.setCellStyle(styleMap.get("HEADSTYLE5"));
		
		cell = (HSSFCell) row.createCell(28);
		cell.setCellValue("5.4");
		cell.setCellStyle(styleMap.get("HEADSTYLE5"));
		
		cell = (HSSFCell) row.createCell(29);
		cell.setCellValue("6.1");
		cell.setCellStyle(styleMap.get("HEADSTYLE5"));
		
		cell = (HSSFCell) row.createCell(30);
		cell.setCellValue("6.2");
		cell.setCellStyle(styleMap.get("HEADSTYLE5"));
		
		cell = (HSSFCell) row.createCell(31);
		cell.setCellValue("6.3");
		cell.setCellStyle(styleMap.get("HEADSTYLE5"));
		
		cell = (HSSFCell) row.createCell(32);
		cell.setCellValue("...");
		cell.setCellStyle(styleMap.get("HEADSTYLE6"));
		
		
		row = (HSSFRow) hSheet.createRow(8);
		createBlanCell(styleMap, row, 4);
		
		cell = (HSSFCell) row.createCell(4);
		range = new CellRangeAddress(8, 8, 4, 7);
		hSheet.addMergedRegion(range);
		cell.setCellValue("期初余额");
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		setRegionBorder(new Integer[]{1, 1, 1, 1}, range, hSheet, hwb);
		
		createBlanCell(styleMap, row, 8, 33);
		
		for (int i = 9; i < 20; i++) {
			row = (HSSFRow) hSheet.createRow(i);
			createBlanCell(styleMap, row, 33);
		}
		
		row = (HSSFRow) hSheet.createRow(20);
		createBottomBlanCell(styleMap, row, 0, 4);
		
		cell = (HSSFCell) row.createCell(4);
		range = new CellRangeAddress(20, 20, 4, 7);
		hSheet.addMergedRegion(range);
		cell.setCellValue("期末余额");
		cell.setCellStyle(styleMap.get("HEADSTYLE4"));
		setRegionBorder(new Integer[]{2, 1, 1, 1}, range, hSheet, hwb);
		
		createBottomBlanCell(styleMap, row, 8, 33);
		
		hwb.write(os);
		hwb.close();
		os.close();
	}

	private static void createBlanCell(Map<String, CellStyle> styleMap,
			HSSFRow row, int endColumnIndex) {
		
		HSSFCell cell;
		for (int i = 0; i < endColumnIndex; i++) {
			cell = (HSSFCell) row.createCell(i);
			if (i < 32) {
				cell.setCellStyle(styleMap.get("HEADSTYLE5"));
			} else {
				cell.setCellStyle(styleMap.get("HEADSTYLE6"));
			}
		}
	}
	
	private static void createBlanCell(Map<String, CellStyle> styleMap,
			HSSFRow row, int startColumnIndex, int endColumnIndex) {
		
		HSSFCell cell;
		for (int i = startColumnIndex; i < endColumnIndex; i++) {
			cell = (HSSFCell) row.createCell(i);
			if (i < 32) {
				cell.setCellStyle(styleMap.get("HEADSTYLE5"));
			} else {
				cell.setCellStyle(styleMap.get("HEADSTYLE6"));
			}
		}
	}
	
	private static void createBottomBlanCell(Map<String, CellStyle> styleMap,
			HSSFRow row, int startColumnIndex, int endColumnIndex) {
		
		HSSFCell cell;
		for (int i = startColumnIndex; i < endColumnIndex; i++) {
			cell = (HSSFCell) row.createCell(i);
			if (i < 32) {
				cell.setCellStyle(styleMap.get("HEADSTYLE7"));
			} else {
				cell.setCellStyle(styleMap.get("HEADSTYLE8"));
			}
		}
	}

	public static Map<String, CellStyle> initStyleMap(Workbook wb, Sheet sheet) {
		
		Map<String, CellStyle> styleMap = new HashMap<String, CellStyle>();
		
		/* headStyle1 */
		Font font = wb.createFont();
		font.setFontHeightInPoints((short)16);
		font.setFontName("仿宋");
		font.setBold(true);
		CellStyle style = wb.createCellStyle();
		style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
		style.setFont(font);
		styleMap.put("HEADSTYLE1", style);
		
		/* headStyle2 */
		font = wb.createFont();
		font.setFontHeightInPoints((short)10);
		font.setFontName("仿宋");
		font.setBold(true);
		style = wb.createCellStyle();
		style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
		style.setWrapText(true);
		style.setFont(font);
		styleMap.put("HEADSTYLE2", style);
		
		/* headStyle3 */
		font = wb.createFont();
		font.setFontHeightInPoints((short)10);
		font.setFontName("仿宋");
		style = wb.createCellStyle();
		style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
		style.setWrapText(true);
		style.setFont(font);
		styleMap.put("HEADSTYLE3", style);
		
		/* headStyle4 */
		font = wb.createFont();
		font.setFontHeightInPoints((short)10);
		font.setFontName("仿宋");
		style = wb.createCellStyle();
		style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
		style.setWrapText(true);		
		style.setFont(font);
		styleMap.put("HEADSTYLE4", style);
		
		/* headStyle5 */
		font = wb.createFont();
		font.setFontHeightInPoints((short)10);
		font.setFontName("仿宋");
		style = wb.createCellStyle();
		style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
		style.setWrapText(true);	
		style.setBorderBottom((short)1);
		style.setBorderLeft((short)1);
		style.setBorderRight((short)1);
		style.setBorderTop((short)1);
		style.setFont(font);
		styleMap.put("HEADSTYLE5", style);
		
		/* headStyle6 */
		font = wb.createFont();
		font.setFontHeightInPoints((short)10);
		font.setFontName("仿宋");
		style = wb.createCellStyle();
		style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
		style.setWrapText(true);	
		style.setBorderBottom((short)1);
		style.setBorderLeft((short)1);
		style.setBorderRight((short)2);
		style.setBorderTop((short)1);
		style.setFont(font);
		styleMap.put("HEADSTYLE6", style);
		
		/* headStyle7 */
		font = wb.createFont();
		font.setFontHeightInPoints((short)10);
		font.setFontName("仿宋");
		style = wb.createCellStyle();
		style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
		style.setWrapText(true);	
		style.setBorderBottom((short)2);
		style.setBorderLeft((short)1);
		style.setBorderRight((short)1);
		style.setBorderTop((short)1);
		style.setFont(font);
		styleMap.put("HEADSTYLE7", style);
		
		/* headStyle8 */
		font = wb.createFont();
		font.setFontHeightInPoints((short)10);
		font.setFontName("仿宋");
		style = wb.createCellStyle();
		style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
		style.setWrapText(true);	
		style.setBorderBottom((short)2);
		style.setBorderLeft((short)1);
		style.setBorderRight((short)2);
		style.setBorderTop((short)1);
		style.setFont(font);
		styleMap.put("HEADSTYLE8", style);
		
		/* cotentStyle */
		font = wb.createFont();
		font.setFontHeightInPoints((short)10);
		font.setFontName("仿宋");
		style = wb.createCellStyle();
		style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
		style.setFont(font);
		styleMap.put("COTENTSTYLE", style);
		
		/* remark1Style */
		font = wb.createFont();
		font.setFontHeightInPoints((short)12);
		font.setFontName("黑体");
		style = wb.createCellStyle();
		style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
		style.setFont(font);
		styleMap.put("REMARKSTYLE1", style);
		
		
		return styleMap;
	}
	
	private static void setRegionBorder(Integer[] borders, CellRangeAddress region, Sheet sheet,Workbook wb){
		RegionUtil.setBorderBottom(borders[0],region, sheet, wb);
		RegionUtil.setBorderLeft(borders[1], region, sheet, wb);
		RegionUtil.setBorderRight(borders[2],region, sheet, wb);
		RegionUtil.setBorderTop(borders[3],region, sheet, wb);
	
	}
	
	private static void setCellBorder(Integer[] borders, HSSFCell cell, Sheet sheet,Workbook wb){
		
	}
	
	private static void setRegionWidth(int width, CellRangeAddress region, Sheet sheet) {
		
		int colStart = region.getFirstColumn();
		int colEnd = region.getLastColumn();
		int pWidth = width / (colEnd - colStart + 1);
		sheet.setColumnWidth(colStart, width);
		for (int i = colStart; i <= colEnd; i++) {
			sheet.setColumnWidth(i, pWidth);
		}
	}


}
